from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    Numeric,
    String,
    ForeignKey,
)

from sqlalchemy.orm import relationship, declarative_base
from pprint import pprint
from sqlalchemy.ext.hybrid import hybrid_property
from en_url import engine

try:
    # 创建声明式的基类，所有的模型类都要继承自这个基类
    Base = declarative_base()

    # 定义User模型类
    class User(Base):
        # 指定数据库的表名为users
        __tablename__ = "users"
        id = Column(Integer, primary_key=True)
        name = Column(String(50))
        fullname = Column(String(50))
        # 定义与Address表一对多的关系，back_populates指定反向引用
        addresses = relationship("Address", back_populates="user")

        # 使用hybrid_property装饰器定义混合属性
        @hybrid_property
        def description(self):
            email_addresses = ",".join(
                [address.email_address for address in self.addresses]
            )
            return f"{self.fullname} {email_addresses}"

    class Address(Base):
        # 指定数据库的表名为users
        __tablename__ = "addresses"
        id = Column(Integer, primary_key=True)
        email_address = Column(String(50), nullable=False)
        user_id = Column(Integer, ForeignKey("users.id"))
        user = relationship("User", back_populates="addresses")

    class Account(Base):
        # 指定数据库的表名为users
        __tablename__ = "account"
        id = Column(Integer, primary_key=True)
        user_id = Column(Integer, ForeignKey("users.id"))
        balance = Column(Numeric)

    # 在数据库中创建所有的定义的模型对应的表
    Base.metadata.create_all(engine)

except Exception as e:
    print(f"创建数据库表失败:{e}")

# 导入会话创建器
from sqlalchemy.orm import sessionmaker

# 创建会话工厂，绑定上面定义的数据库引擎
Session = sessionmaker(bind=engine)
# 创建会话对象
session = Session()
page = 1
per_page = 3
users = (
    session.query(User)  # 查询user表
    .order_by(User.id)  # 按主键进行排序
    .offset((page - 1) * per_page)  # 从第几条开始查询
    .limit(per_page)  # 获取几条
    .all()
)
# for user in users:
#    print(user.name)
# 导入SQL聚合函数 count sum max min avg
from sqlalchemy import func

# 查询user表中所有的用户的数量，返回总数
count = session.query(func.count(User.id)).scalar()
print(count)

# 查询每个用户的name以及其对应的地址的数量

result = (
    session.query(User.id, func.count(Address.email_address))
    .join(Address)  # 连接地址表
    .group_by(User.id)  # 按用户ID进行分组
    .all()
)
print(result)
